postgres performance tuning
2023-10-23 ยท 2 min read
use separate disks for DB data and WAL files #
An excerpt from the official postgres manual:
It is advantageous if the WAL is located on a different disk from the main database files. This can be achieved by moving the
pg_waldirectory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.
Related info from an azure cloud disk performance tuning guide:
As an example, you can apply these guidelines to SQL Server running on Premium Storage by doing the following,
- Configure "ReadOnly" cache on premium storage disks hosting data files. a. The fast reads from cache lower the SQL Server query time since data pages are retrieved much faster from the cache compared to directly from the data disks. b. Serving reads from cache, means there is additional Throughput available from premium data disks. SQL Server can use this additional Throughput towards retrieving more data pages and other operations like backup/restore, batch loads, and index rebuilds.
- Configure "None" cache on premium storage disks hosting the log files. a. Log files have primarily write-heavy operations. Therefore, they do not benefit from the ReadOnly cache.